CREATE FUNCTION fnDateToMinute
-- Date in minutes from begining
(
  @Date DATETIME
)
RETURNS BIGINT
AS  
BEGIN 
  RETURN DATEDIFF(MINUTE, '1753-01-01', @Date)
END
GO
ALTER TABLE Ping
ADD DatePingMinute BIGINT NULL 
GO
UPDATE Ping
SET DatePingMinute = dbo.fnDateToMinute(DatePing)
GO
ALTER TABLE Ping
ALTER COLUMN DatePingMinute BIGINT NOT NULL 
GO
CREATE TABLE [dbo].[PromotionPushQueue](
	[PromotionId] [int] NOT NULL,
	[UserId] [int] NOT NULL,
	[DatePush] [datetime] NOT NULL,
	[Latitude] [float] NOT NULL,
	[Longitude] [float] NOT NULL,
	[RatePerPush] [money] NOT NULL,
 CONSTRAINT [PK_PromotionPushQueue] PRIMARY KEY CLUSTERED 
(
	[PromotionId] ASC,
	[UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[PromotionPushQueue]  WITH CHECK ADD  CONSTRAINT [FK_PromotionPushQueue_Promotion] FOREIGN KEY([PromotionId])
REFERENCES [dbo].[Promotion] ([PromotionId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PromotionPushQueue] CHECK CONSTRAINT [FK_PromotionPushQueue_Promotion]
GO
ALTER TABLE [dbo].[PromotionPushQueue]  WITH CHECK ADD  CONSTRAINT [FK_PromotionPushQueue_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([UserId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[PromotionPushQueue] CHECK CONSTRAINT [FK_PromotionPushQueue_User]
GO
ALTER TABLE [dbo].[PromotionPushQueue] ADD  DEFAULT ((0)) FOR [RatePerPush]
GO
ALTER PROCEDURE [dbo].[spPromotionPushQueue_Add]
AS
BEGIN
	SET NOCOUNT ON

    DECLARE @NowInMinute BIGINT
    SET @NowInMinute = dbo.fnDateToMinute(GETUTCDATE())
    
    DECLARE @Now DATETIME, @BegTime DATETIME, @EndTime DATETIME
    SET @Now = GETUTCDATE()
	SET @BegTime = '00:00:00'
	SET @EndTime = '23:59:59'
    
    CREATE TABLE #tmpUsers
    (
		UserId INT,
		RegState VARCHAR(2),
		ZipCode VARCHAR(5),
		RegCity VARCHAR(50),
		Lat FLOAT,
		Lon FLOAT
	)
	
	CREATE TABLE #tmpPromotions
	(
		UserId INT,
		PromotionId INT,
		RatePerPush MONEY
	)
	
	CREATE TABLE #tmpUserBlockCategory
	(
		UserId INT,
		CategoryId INT,
		ParentId INT
	)
	
	CREATE TABLE #tmpUserWantCategory
	(
		UserId INT,
		CategoryId INT,
		ParentId INT
	)
	
	INSERT INTO #tmpUsers
	(UserId, RegState, ZipCode, RegCity, Lat, Lon)
	SELECT Ping.UserId, 
		CASE WHEN [User].RegState = '' THEN NULL ELSE [User].RegState END,
		CASE WHEN [User].ZipCode = '' THEN NULL ELSE [User].ZipCode END,
		CASE WHEN [User].RegCity = '' THEN NULL ELSE [User].RegCity END,
		Ping.Latitude, Ping.Longitude
	FROM (SELECT UserId, MAX(DatePing) AS DatePing
		FROM Ping (NOLOCK)
		WHERE (@NowInMinute - DatePingMinute) <= 10
		GROUP BY UserId) AS vwPing INNER JOIN Ping (NOLOCK) ON (vwPing.UserId = Ping.UserId) AND (vwPing.DatePing = Ping.DatePing)
									INNER JOIN [User] (NOLOCK) ON Ping.UserId = [User].UserId;
	
	WITH BlockCategoryLevelList
	AS
	(
		SELECT BlockCategory.UserId, BlockCategory.CategoryId, NULL AS ParentId
		FROM #tmpUsers INNER JOIN BlockCategory (NOLOCK) ON #tmpUsers.UserId = BlockCategory.UserId
			
		UNION ALL

		SELECT bcll.UserId, c.CategoryId, c.ParentId
		FROM Category (NOLOCK) AS c INNER JOIN BlockCategoryLevelList AS bcll ON c.ParentId = bcll.CategoryId
	)
	INSERT INTO #tmpUserBlockCategory
	(UserId, CategoryId, ParentId)
	SELECT UserId, CategoryId, ParentId
	FROM BlockCategoryLevelList;
	
	WITH WantCategoryLevelList
	AS
	(
		SELECT UserCategory.UserId, UserCategory.CategoryId, NULL AS ParentId
		FROM #tmpUsers INNER JOIN UserCategory (NOLOCK) ON #tmpUsers.UserId = UserCategory.UserId
			
		UNION ALL

		SELECT wcll.UserId, c.CategoryId, c.ParentId
		FROM Category (NOLOCK) AS c INNER JOIN WantCategoryLevelList AS wcll ON c.ParentId = wcll.CategoryId
	)
	INSERT INTO #tmpUserWantCategory
	(UserId, CategoryId, ParentId)
	SELECT UserId, CategoryId, ParentId
	FROM WantCategoryLevelList;
	
	INSERT INTO #tmpUserWantCategory
	(UserId, CategoryId, ParentId)
	SELECT #tmpUsers.UserId, Category.CategoryId, Category.ParentId
	FROM #tmpUsers, Category (NOLOCK)
	WHERE NOT EXISTS(SELECT 1 FROM UserCategory (NOLOCK) WHERE UserCategory.UserId = #tmpUsers.UserId)
	
	INSERT INTO #tmpPromotions
	(UserId, PromotionId, RatePerPush)
	SELECT #tmpUsers.UserId, Promotion.PromotionId, Promotion.RatePerPush
	FROM #tmpUsers, Promotion (NOLOCK) LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
										LEFT JOIN ProximityRadius (NOLOCK) ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
	WHERE (NOT EXISTS(SELECT 1 FROM PromotionPush (NOLOCK) WHERE (PromotionPush.UserId = #tmpUsers.UserId) AND (PromotionPush.PromotionId = Promotion.PromotionId)))
		AND (NOT EXISTS(SELECT 1 FROM PromotionPushQueue (NOLOCK) WHERE (PromotionPushQueue.UserId = #tmpUsers.UserId) AND (PromotionPushQueue.PromotionId = Promotion.PromotionId)))
		AND (NOT EXISTS(SELECT 1 FROM BlockBusiness (NOLOCK) AS bb WHERE bb.BusinessId = Business.BusinessId AND bb.UserId = #tmpUsers.UserId))
		AND (NOT EXISTS(SELECT 1 FROM BlockPromotion (NOLOCK) AS bp WHERE bp.PromotionId = Promotion.PromotionId AND bp.UserId = #tmpUsers.UserId))
		AND (dbo.fnCalculateDistance(#tmpUsers.Lat, #tmpUsers.Lon, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280))
		--AND (dbo.fnCalculateDistance(#tmpUsers.Lat, #tmpUsers.Lon, Business.Latitude, Business.Longitude) >= CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280))		
		AND (Promotion.Deleted = 0)
		AND (Promotion.Ready = 1)
		AND (Promotion.StatusCode = 'ACT')
		AND (Business.IsActive = 1)
		AND (NOT EXISTS(SELECT 1 
						FROM PromotionCategory (NOLOCK) AS pc INNER JOIN #tmpUserBlockCategory AS bc ON pc.CategoryId = bc.CategoryId
						WHERE pc.PromotionId = Promotion.PromotionId
							AND bc.UserId = #tmpUsers.UserId))
		AND (EXISTS(SELECT 1 
					FROM PromotionCategory AS pc INNER JOIN #tmpUserWantCategory AS uc ON pc.CategoryId = uc.CategoryId
					WHERE pc.PromotionId = Promotion.PromotionId
						AND uc.UserId = #tmpUsers.UserId))
		AND ((Promotion.RegisteredOutsideOfState = 0) OR ((Promotion.RegisteredOutsideOfState = 1) AND (ISNULL(#tmpUsers.RegState, Business.StateCode) <> Business.StateCode)))
				AND ((Promotion.RegisteredOutsideOfZip = 0) OR ((Promotion.RegisteredOutsideOfZip = 1) AND (ISNULL(#tmpUsers.ZipCode, Business.ZipCode) <> Business.ZipCode)))
				AND ((Promotion.RegisteredOutsideOfCity = 0) OR ((Promotion.RegisteredOutsideOfCity = 1) AND (ISNULL(#tmpUsers.RegCity, Business.City) <> Business.City)))
				AND (CONVERT(TIME, @Now) BETWEEN CONVERT(TIME, ISNULL(Promotion.BaseCostTimingStartsWithinDay, @BegTime)) AND CONVERT(TIME, ISNULL(Promotion.BaseCostTimingEndsWithinDay, @EndTime)))
				
	INSERT INTO PromotionPushQueue
	(PromotionId, UserId, DatePush, Latitude, Longitude, RatePerPush)
	SELECT #tmpPromotions.PromotionId, #tmpUsers.UserId, 
		GETUTCDATE(), #tmpUsers.Lat, #tmpUsers.Lon, #tmpPromotions.RatePerPush
	FROM #tmpPromotions	LEFT JOIN #tmpUsers ON #tmpPromotions.UserId = #tmpUsers.UserId
	
	DROP TABLE #tmpUsers
	DROP TABLE #tmpPromotions
	DROP TABLE #tmpUserBlockCategory
	DROP TABLE #tmpUserWantCategory
END
GO